package com.ybfenpan.utils;

import com.ybfenpan.tracer.Debug;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

/**
 * Excel处理类
 * @author qiyh
 */
public class ExcelUtils {
    private static String defaultSheetName = "new Sheet";

    /**
     * 用查询的结果集导出Excel<br>
     * 从list里取出LinkedHashMap,每个map是一行,SQL中的字段顺序必须与header中一致<br>
     * xml中必须是resultClass="java.util.LinkedHashMap"
     * @param headRow 标题数组
     * @param resultList 存放MAP的List
     * @return HSSFWorkbook
     * @qiyh
     */
    public static HSSFWorkbook createWb1(String[] headRow, List<LinkedHashMap> resultList) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 样式
        HSSFCellStyle cs = getTitleStyle(wb);
        // 建立新的sheet对象
        HSSFSheet sheet = wb.createSheet(defaultSheetName);
        HSSFCell csCellHead = null;
        // 设置标头
        HSSFRow row0 = sheet.createRow(0);
        if (null != headRow) {
            for (int i = 0; i < headRow.length; i++) {
                csCellHead = row0.createCell(i);
                csCellHead.setCellType(HSSFCell.ENCODING_UTF_16);
                csCellHead.setCellValue(String.valueOf(headRow[i]));
                csCellHead.setCellStyle(cs);
            }
        }
        // 设置内容
        if (null != resultList) {
            int rowIdx = sheet.getLastRowNum() + 1;
            int retSize = resultList.size();
            for (int j = 0; j < retSize; j++) {
                // System.out.println("第"+(j+1)+"行： ");
                LinkedHashMap retMap = resultList.get(j);
                // 建立新行
                HSSFRow sheetRow = sheet.createRow(rowIdx);
                Set<Object> keySet = retMap.keySet(); // 获取map的key值的集合，set集合

                // 序号（第1列）
                HSSFCell csCell = sheetRow.createCell(0);
                csCell.setCellType(HSSFCell.ENCODING_UTF_16);
                csCell.setCellValue(String.valueOf(j + 1));

                // 值（第1列起）
                int k = 1;
                for (Object key : keySet) { // 遍历key
                    if (j == 0) {
                        csCellHead = row0.getCell(k);
                        if (null == csCellHead || null==csCellHead.getStringCellValue()) {
                            csCellHead = row0.createCell(k);
                            csCellHead.setCellType(HSSFCell.ENCODING_UTF_16);
                            csCellHead.setCellValue(String.valueOf(key));
                            csCellHead.setCellStyle(cs);
                        }
                    }
                    String cellVal = getObjVal(retMap.get(key));
                    // System.out.print(keyObj+":"+cellVal+", ");//输出键与值
                    // 建立新行单元
                    csCell = sheetRow.createCell(k);
                    // 设置cell编码，解决中文高位字节截断
                    csCell.setCellType(HSSFCell.ENCODING_UTF_16);
                    // 设置中英文结合字符串
                    csCell.setCellValue(cellVal);
                    k++;
                }
                rowIdx++;
            }
            Debug.println(">>>>>>>导出列表数为：" + retSize);
        }
        return wb;
    }

    /**
     * 方法名称：createWb 内容摘要：从传入的列表dataList中按顺序取出里面的列表arrayList,然后从arrayList得到所有的String写到一行中
     * @param headRow headRow
     * @param infoList infoList
     * @return HSSFWorkbook
     * @qiyh
     */
    public static HSSFWorkbook createWb2(String[] headRow, List infoList) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 样式
        HSSFCellStyle cs = getTitleStyle(wb);
        // 建立新的sheet对象
        HSSFSheet sheet = wb.createSheet(defaultSheetName);
        HSSFCell csCell = null;
        // 设置标头
        HSSFRow row0 = sheet.createRow(0);
        if (null != headRow) {
            for (int i = 0; i < headRow.length; i++) {
                csCell = row0.createCell(i);
                csCell.setCellType(HSSFCell.ENCODING_UTF_16);
                csCell.setCellValue(String.valueOf(headRow[i]));
                csCell.setCellStyle(cs);
            }
        }
        // 设置内容
        int rowIdx = sheet.getLastRowNum() + 1;
        if (null != infoList) {
            int retSize = infoList.size();
            for (int j = 0; j < retSize; j++) {
                // 定义子列表
                List subDataList = null;
                // 获取列表中的对象
                Object listObject = infoList.get(j);
                // 判断列表中的对象是否属于List类型
                if (listObject instanceof List) {
                    // 将列表中的对象赋值给子列表
                    subDataList = (List) listObject;
                    if (subDataList != null && subDataList.size() != 0) {
                        // 建立新行
                        HSSFRow sheetRow = sheet.createRow(rowIdx);
                        // 循环取得子列表中的值
                        for (int k = 0; k < subDataList.size(); k++) {
                            // 建立新行单元
                            csCell = sheetRow.createCell(k);
                            // 设置cell编码，解决中文高位字节截断
                            csCell.setCellType(HSSFCell.ENCODING_UTF_16);
                            // 设置中英文结合字符串
                            csCell.setCellValue(getObjVal(subDataList.get(k)));
                        }
                        // 行号递增
                        rowIdx++;
                    }
                }
            }
            Debug.println(">>>>>>>导出列表数为：" + retSize);
        }
        return wb;
    }

    /**
     * 带有说明文字的导出. 方法名称：createWbII 内容摘要：
     * 从传入的列表dataList中按顺序取出里面的列表arrayList,然后从arrayList得到所有的String写到一行中
     * @param dataList : 要导出的数据列表.<BR/>
     * @param comment : 要添加的说明文字.<BR/>
     * @param startA : 起始行.<BR/>
     * @param startV : 其实列.<BR/>
     * @param endA : 结束行.<BR/>
     * @param endV : 结束列.<BR/>
     * @return HSSFWorkbook
     */
    public static HSSFWorkbook createWbPro(List dataList, String comment, int startA, int startV, int endA, int endV) {
        HSSFWorkbook wb = null;
        try {
            // 判断列表是否存在
            if (dataList == null || dataList.size() == 0) {
                return null;
            }
            wb = new HSSFWorkbook();
            // 建立新的HSSFSheet对象
            HSSFSheet sheet = wb.createSheet(defaultSheetName);
            // 设置说明问题
            sheet.addMergedRegion(new CellRangeAddress(startA, (startV), endA, (endV))); // 起始行、起始列、结束行、结束列
            // 样式
            HSSFCellStyle style = wb.createCellStyle();
            HSSFFont font = wb.createFont();
            font.setColor(HSSFFont.COLOR_RED);
            style.setFont(font);
            style.setAlignment((short) 1);
            style.setVerticalAlignment((short) 0);
            style.setWrapText(true);

			HSSFRow row0 = sheet.createRow(0);
            HSSFCell csCell1 = row0.createCell(0);
            csCell1.setCellType(HSSFCell.CELL_TYPE_STRING);
            csCell1.setCellValue(comment);
            csCell1.setCellStyle(style);

            int sheetRow = startA + 1;
            // 循环取列表中的值
            for (int i = 0; i < dataList.size(); i++) {
                // 定义子列表
                List subDataList = null;
                // 获取列表中的对象
                Object listObject = dataList.get(i);
                // 判断列表中的对象是否属于List类型
                if (listObject instanceof List) {
                    // 将列表中的对象赋值给子列表
                    subDataList = (List) listObject;
                    if (subDataList != null && subDataList.size() != 0) {
                        // 建立新行
                        HSSFRow rowId = sheet.createRow(sheetRow);
                        // 循环取得子列表中的值
                        for (int j = 0; j < subDataList.size(); j++) {
                            // 建立新行单元
                            HSSFCell csCell = rowId.createCell(j);
                            // 设置cell编码，解决中文高位字节截断
                            csCell.setCellType(HSSFCell.ENCODING_UTF_16);
                            // 设置中英文结合字符串
                            csCell.setCellValue(getObjVal(subDataList.get(j)));
                        }
                        // 行号递增
                        sheetRow++;
                    }
                }
            }
        } catch (Exception ep) {
            ep.printStackTrace();
        }
        return wb;
    }

    /**
     * @param obj obj
     * @return String
     */
    public static String getObjVal(Object obj) {
        return StringUtil.getDbStr(obj);
    }

    /**
     * 获取标题样式
     * @param wb wb
     * @return HSSFCellStyle
     */
    private static HSSFCellStyle getTitleStyle(HSSFWorkbook wb) {
        // 样式
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setWrapText(false);
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 背景颜色
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充样式
        return cs;
    }

    /**
     * 检查导入Excel是否符合
     * @param heads heads
     * @param row row
     * @return boolean
     */
    public static boolean checkExcelIn(String[] heads, HSSFRow row) {
        boolean flag = true;
        for (int i = 0; i < heads.length; i++) {
            HSSFCell cell = row.getCell(i);
            String cellVal = getStringCell(cell);
            if (!heads[i].equals(cellVal)) {
                return false;
            }
        }
        return flag;
    }

    /**
     * 对excel表格的值进行处理.
     * 
     * @param cell cell
     * @return str
     */
    public static String getStringCell(HSSFCell cell) {
        String str = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA:
                    str = String.valueOf(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    str = cell.getStringCellValue().trim();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    str = String.valueOf(cell.getBooleanCellValue());
                    break;
                default:
                    break;
            }
        }
        return str;
    }

    /**
    * @Title: getNumericCell
    * @param cell cell
    * @return double
    * @author: Qiyh 2013-12-19
    */
    public static double getNumericCell(HSSFCell cell) {
        double doub = 0;
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA:
                    doub = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    String val = cell.getStringCellValue();
                    doub = Double.parseDouble(StringUtils.isBlank(val) ? "0" : val.trim());
                    break;
                default:
                    break;
            }
        }
        return doub;
    }

    /**
    * @Title: getLongCell
    * @param cell cell
    * @return long
    * @author: Qiyh 2013-12-19
    */
    public static long getLongCell(HSSFCell cell) {
        return (long) getNumericCell(cell);
    }

    /**
    * @Title: getIntCell
    * @param cell cell
    * @return int
    * @author: Qiyh 2013-12-19
    */
    public static int getIntCell(HSSFCell cell) {
        return (int) getNumericCell(cell);
    }

    /**
    * @Title: getDateCell
    * @param cell cell
    * @return String
    * @author: Qiyh 2013-12-19
    */
    public static String getDateCell(HSSFCell cell) {
        String date = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    date = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                case HSSFCell.CELL_TYPE_BOOLEAN:
                case HSSFCell.CELL_TYPE_ERROR:
                    break;
                default:
                    date = DateUtil.dateFormat(cell.getDateCellValue(), DateUtil.dateFmt);
                    break;
            }
        }
        return date;
    }

    /**
     * [{列1:val,列2:val2,列3:val3...},{列1:val,列2:val2,列3:val3...},...]：一行对应数组中的一个元素，数组lenth表示行数
     * @param xlsPath
     * @param sheedIdx:从0开始
     * @return
     * @throws Exception
     */
    public static LinkedHashMap<String, String>[] getStContMapArr(String xlsPath, int sheedIdx) throws Exception {
        File file = new File(xlsPath);
        if(!file.exists()){
            throw new Exception("文件不存在!\r\n"+xlsPath);
        }
        FileInputStream fis = new FileInputStream(file);
        try {
            // 生成一个POIFSFileSystem对象
            POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(fis));
            // POIFSFileSystem对象构造一个HSSFWorkbook
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            // 全局验证
            HSSFSheet sheet = wb.getSheetAt(sheedIdx);
            if (sheet.getLastRowNum() > 0) {
                //取得总记录行数   (包括标题列)
                int rowsint = sheet.getLastRowNum();//从0开始
                int colsint = sheet.getRow(0).getPhysicalNumberOfCells();

                // 获取标题
                String[] titleArr = new String[colsint];
                HSSFRow row0 = sheet.getRow(0);
                for(int j=0; j<colsint; j++){
                    HSSFCell cell = row0.getCell(j);
                    String cellVal = getStringCell(cell);
                    titleArr[j] = cellVal;
                }

                LinkedHashMap[] exlArr = new LinkedHashMap[rowsint];// rowsint-1 去掉标题
                // 验证表头是否符合模板要求
                for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
                    try {
                        LinkedHashMap<String, String> m = new LinkedHashMap<String, String>();
                        HSSFRow row = sheet.getRow(i);
                        if(null == row)continue;
                        for(int j=0; j<colsint; j++){
                            HSSFCell cell = row.getCell(j);
                            String cellVal = getStringCell(cell);
                            m.put(titleArr[j], cellVal);
                        }
                        exlArr[i-1] = m;
                    }catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                return exlArr;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            fis.close();
        }
        return null;
    }

    /**
     * @param xlsPath
     * @param sheetName
     * @return
     * @throws Exception
     */
    public static LinkedHashMap<String, String>[] getStContMapArr(String xlsPath, String sheetName) throws Exception {
        File file = new File(xlsPath);
        if(!file.exists()){
            throw new Exception("文件不存在!\r\n"+xlsPath);
        }
        FileInputStream fis = new FileInputStream(file);
        try {
            // 生成一个POIFSFileSystem对象
            POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(fis));
            // POIFSFileSystem对象构造一个HSSFWorkbook
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            int sheedIdx = wb.getSheetIndex(sheetName);
            if(sheedIdx < 0){
                throw new Exception("没有找到对应的sheet："+sheetName+"!");
            }
            return getStContMapArr(xlsPath, sheedIdx);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            fis.close();
        }
        return null;
    }


}
